//
//  SalesOrderDBObject.m
//  POS
//
//  Created by sigma xu on 4/22/13.
//  Copyright (c) 2013 sigma xu. All rights reserved.
//
#import "SalesOrderDBO.h"

@implementation SalesOrderDBO

@synthesize so_id,so_number,create_date,sales_man,total_amount,total_item,status,client,remark;
-(id)init{
    if(self = [super init]){
        NSString* docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
        NSString* dbPath = [docPath stringByAppendingPathComponent:@"pos.sqlite"];
        
        if (sqlite3_open([dbPath UTF8String], &posDB) != SQLITE_OK) {
            NSLog(@"Failed to open database!");
        }
    }
    return self;
}

-(NSMutableArray *) get_all_sales_order{
    
    sales_order_list = [[NSMutableArray alloc] init];
    const char *sql = "select * from sales_order where status > 0 order by create_date desc";
    if(sqlite3_prepare(posDB, sql, -1, &sqlstmt, NULL)!=SQLITE_OK){
        NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
    }else{
        while (sqlite3_step(sqlstmt) == SQLITE_ROW) {
            SalesOrderDBO *sodbo = [[SalesOrderDBO alloc] init];
            sodbo.so_id = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt, 0)];
            sodbo.so_number = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt, 1)];
            NSDate *datefromdb=[NSDate dateWithTimeIntervalSince1970:sqlite3_column_double(sqlstmt, 2)];
            NSDateFormatter *df_utc = [[NSDateFormatter alloc] init];
            [df_utc setTimeZone:[NSTimeZone localTimeZone]];
            [df_utc setDateFormat:@"yyy-MM-dd HH:mm:ss"];
            sodbo.create_date = [df_utc stringFromDate:datefromdb];
            sodbo.sales_man = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt, 3)];
            sodbo.total_amount = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt, 4)];
            sodbo.total_item = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt, 5)];
            sodbo.status = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt, 6)];
            sodbo.client = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt, 7)];
            sodbo.remark = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt, 8)];
            [sales_order_list addObject:sodbo];            
        }
    }
    return sales_order_list;
    
}
-(SalesOrderDBO *) get_sales_order_by_so_id{
    SalesOrderDBO *sodbo = [[SalesOrderDBO alloc] init];
    NSString *query = [NSString stringWithFormat:@"SELECT * from sales_order where id_so = '%@' and status > 0", so_id];
    const char *sql = [query UTF8String];
    if(sqlite3_prepare(posDB, sql, -1, &sqlstmt, NULL)!=SQLITE_OK){
        NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
    }else{
        sqlite3_bind_text(sqlstmt, 0, [so_id UTF8String], -1, SQLITE_TRANSIENT);
        if (sqlite3_step(sqlstmt) == SQLITE_ROW) {
            
            sodbo.so_id = [NSString stringWithUTF8String:(char *) sqlite3_column_text(sqlstmt, 0)];
            sodbo.so_number = [NSString stringWithUTF8String:(char *) sqlite3_column_text(sqlstmt, 1)];
            NSDate *datefromdb=[NSDate dateWithTimeIntervalSince1970:sqlite3_column_double(sqlstmt, 2)];
            NSDateFormatter *df_utc = [[NSDateFormatter alloc] init];
            [df_utc setTimeZone:[NSTimeZone localTimeZone]];
            [df_utc setDateFormat:@"yyy-MM-dd HH:mm:ss"];
            sodbo.create_date = [df_utc stringFromDate:datefromdb];
            sodbo.sales_man = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt, 3)];
            sodbo.total_amount = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt, 4)];
            sodbo.total_item = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt, 5)];
            sodbo.status = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt, 6)];
            sodbo.client = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt, 7)];
            sodbo.remark = [[NSString alloc] initWithFormat:@"%s",sqlite3_column_text(sqlstmt, 8)];
        }
        sqlite3_reset(sqlstmt);
    }
    return sodbo;
    
}
-(int)insert_sales_order{
    int last_so_id = 0;
    const char *sql = "insert into sales_order (so_number, create_date, sales_man, total_amount, total_item, status,client,remark) values (?,?,?,?,?,?,?,?)";
    if(sqlite3_prepare(posDB, sql, -1, &sqlstmt, NULL)!=SQLITE_OK){
        NSLog(@"Problem with prepare statement 1: '%s'",sqlite3_errmsg(posDB));
    }else{
        sqlite3_bind_text(sqlstmt, 1, [so_number UTF8String], -1, SQLITE_TRANSIENT);
        NSDate *now=[[NSDate alloc]init];
        sqlite3_bind_double(sqlstmt, 2, [now timeIntervalSince1970]);
        sqlite3_bind_text(sqlstmt, 3, [sales_man  UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(sqlstmt, 4, [total_amount  UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(sqlstmt, 5, [total_item  UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(sqlstmt, 6, [status  UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(sqlstmt, 7, [client  UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(sqlstmt, 8, [remark  UTF8String], -1, SQLITE_TRANSIENT);
        if(SQLITE_DONE != sqlite3_step(sqlstmt))
            NSLog(@"Problem with prepare statement 2: '%s'",sqlite3_errmsg(posDB));
        else{
            last_so_id =sqlite3_last_insert_rowid(posDB);
        }
        sqlite3_reset(sqlstmt);
    }
    return last_so_id;
}
-(void) update_sales_order{
    NSString *query = [NSString stringWithFormat:@"update sales_order Set so_number = '%@', sales_man = '%@', total_amount = '%@', total_item = '%@', status = '%@', client = '%@', remark = '%@' Where id_so = '%@'",so_number,sales_man,total_amount,total_item,status,client,remark,so_id];
    const char *sql = [query UTF8String];
    if(sqlite3_prepare(posDB, sql, -1, &sqlstmt, NULL)!=SQLITE_OK){
        NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
    }else{
        if(SQLITE_DONE != sqlite3_step(sqlstmt)){
            NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
        }
        sqlite3_reset(sqlstmt);
    }
}
-(void) update_sales_order_number{
    NSString *query = [NSString stringWithFormat:@"update sales_order Set sales_man = '%@', total_amount = '%@', total_item = '%@', status = '%@', client = '%@', remark = '%@' Where id_so = '%@'",sales_man,total_amount,total_item,status,client,remark,so_id];
    const char *sql = [query UTF8String];
    if(sqlite3_prepare(posDB, sql, -1, &sqlstmt, NULL)!=SQLITE_OK){
        NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
    }else{
        if(SQLITE_DONE != sqlite3_step(sqlstmt)){
            NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
        }
        sqlite3_reset(sqlstmt);
    }
}
-(void) delete_sales_order{
//    NSString *query = [NSString stringWithFormat:@"delete from sales_order Where so_number = '%@'",so_number];
//    const char *sql = [query UTF8String];
//    NSLog(@"sql is %s",sql);
//    if(sqlite3_prepare(posDB, sql, -1, &sqlstmt, NULL)!=SQLITE_OK){
//        NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
//    }else{
//        if(SQLITE_DONE != sqlite3_step(sqlstmt)){
//            NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
//        }
//        sqlite3_reset(sqlstmt);
//    }
    NSString *query = [NSString stringWithFormat:@"update sales_order Set status = 0 Where so_number = '%@'",so_number];
    const char *sql = [query UTF8String];
    if(sqlite3_prepare(posDB, sql, -1, &sqlstmt, NULL)!=SQLITE_OK){
        NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
    }else{
        if(SQLITE_DONE != sqlite3_step(sqlstmt)){
            NSLog(@"Problem with prepare statement : '%s'",sqlite3_errmsg(posDB));
        }
        sqlite3_reset(sqlstmt);
    }
}
-(void)finalize{
    sqlite3_close(posDB);
    [super finalize];
}

@end
